﻿using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using seejee_Backendium.Core.Models;
using seejee_Backendium.Data.DbContest;
using seejee_Backendium.Data.Services;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection.PortableExecutable;
using System.Text;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace seejee_Backendium.Data.Repositores
{
    public class UserRepository
    {
        private readonly string _connectionString;
        private readonly IConfiguration _configuration;

        public UserRepository(string connectionString, IConfiguration configuration)
        {
            _connectionString = connectionString;
            _configuration = configuration;
        }

        //对比密码
        public bool ValidateUser(string cCusName, string cCusPassword)
        {
            DataTable userTable = GetUserByUsername(cCusName);
            if (userTable.Rows.Count == 0)
            {
                return false;
            }

            string storedPassword = userTable.Rows[0]["cCusPassword"].ToString();
            string storedSalt = userTable.Rows[0]["Salt"].ToString();
            var finalHash = PasswordService.HashPassword(cCusPassword, storedSalt);
            return string.Equals(finalHash, storedPassword, StringComparison.Ordinal);
        }
        public DataTable GetUserByUsername(string cCusName)
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                //connection.ConnectionString = "Server = 192.168.0.172; Database = seejee_Demo01; User ID = sa; Password = 123456; TrustServerCertificate = true; Connect Timeout = 30";
                connection.Open();
                string query = "SELECT cCusPassword,Salt,iCusPermissions FROM [User] WHERE cCusName = @cCusName";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@cCusName", cCusName);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;

                }
            }
        }

        //获取所有用户
        public PagedResult<UserInfoDTO> GetAllUsers(int pageIndex, int pageSize, string keyword)
        {
            var users = new List<UserInfoDTO>();
            int totalCount = 0;
            // 从配置读取连接字符串
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            {
                connection.Open();
                // 获取总数
                string countSql = "SELECT COUNT(*) FROM CusUserList WHERE cCusName = '' OR cCusName LIKE '%' + @keyword + '%' and iCusPermissions=1";
                using (SqlCommand countCmd = new SqlCommand(countSql, connection))
                {
                    countCmd.Parameters.AddWithValue("@keyword", keyword);
                    totalCount = (int)countCmd.ExecuteScalar();
                }
                string query = "select u.iCusID,u.cCusName,u.iCusPermissions,u.dCusCreateTime,u.cCusUserKey,l.dCusExpiryDate from CusUserList u left join Licenses l on u.cCusName=l.cCusName where u.cCusName = '' OR u.cCusName LIKE '%' + @keyword + '%' and u.iCusPermissions=1  ORDER BY u.cCusName OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;;";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Offset", (pageIndex - 1) * pageSize);
                    command.Parameters.AddWithValue("@PageSize", pageSize);
                    command.Parameters.AddWithValue("@keyword", keyword);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            users.Add(new UserInfoDTO
                            {
                                iCusID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                                cCusName = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                //cCusRealName = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                iCusPermissions = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
                                //dCusCreateTime = reader.IsDBNull(4) ? "" : reader.GetDateTime(4).ToString("yyyy-MM-dd HH:mm:ss"),
                                dCusCreateTime = reader.GetDateTime(3),
                                cCusUserKey = reader.IsDBNull(4) ? "" : reader.GetString(4),
                                dCusExpiryDate = reader.IsDBNull(5) ? DateOnly.MinValue : reader.GetFieldValue<DateOnly>(5)
                            });
                            Console.WriteLine(users);
                        }
                    }
                }
            }
            return new PagedResult<UserInfoDTO>
            {
                Items = users,
                TotalCount = totalCount
            };
        }

        //获取单用户
        public User GetUserById(String cCusName)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                string query = "SELECT u.iCusID, u.cCusName, u.iCusPermissions, u.cCusUserKey,l.dCusExpiryDate FROM CusUserList u left join Licenses l on u.cCusName=l.cCusName WHERE u.cCusName = @cCusName";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@cCusName", cCusName);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            User user = new User
                            {
                                iCusID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                                cCusName = reader.IsDBNull(1) ? "" : reader.GetString(1),
                                //cCusPassword = reader.IsDBNull(2) ? "" : reader.GetString(2),
                                //cCusRealName = reader.IsDBNull(3) ? "" : reader.GetString(3),
                                iCusPermissions = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
                                cCusUserKey = reader.IsDBNull(3) ? "" : reader.GetString(3),
                                //dCusCreateTime = reader[6] is DateTime dt ? dt.ToString("yyyy-MM-dd HH:mm:ss") : reader[6]?.ToString() ?? "",
                                //dCusExpiryDate = reader.FieldCount > 6 && !reader.IsDBNull(6) ? reader.GetDateTime(6) : DateTime.MinValue
                                //dCusExpiryDate = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6).Date
                                dCusExpiryDate = reader.IsDBNull(4) ? DateOnly.MinValue : reader.GetFieldValue<DateOnly>(4) // 直接映射到 DateOnly 类型 

                            };
                            return user;
                        }
                    }
                }
            }
            return null;
        }
        public bool UserNameSearch(string cCusName)
        {
            int totalCount = 0;
            // 从配置读取连接字符串
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection connection = new SqlConnection(connStr))
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    string countSql = "SELECT COUNT(*) FROM CusUserList";
                    using (SqlCommand countCmd = new SqlCommand(countSql, connection))
                    {
                        totalCount = (int)countCmd.ExecuteScalar();
                        if (totalCount == 0)
                        {
                            return true;
                        }
                        return false;
                    }
                }

            }
        }

        //新增用户
        public bool InsertUser(UserInsertDTO users)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))

            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 插入主表 (Users)
                        string insertMainSql = @"INSERT INTO CusUserList (cCusName, iCusPermissions, cCusUserKey,dCusCreateTime) VALUES (@cCusName,  @iCusPermissions, @cCusUserKey,@dCusCreateTime)          
                                                SELECT SCOPE_IDENTITY(); ";
                        // 插入主表 (Users)
                        //string insertMainSql = @"
                        //INSERT INTO [User] (cCusName, cCusPassword, cCusRealName, iCusPermissions, cCusUserKey,dCusCreateTime) 
                        //VALUES (@cCusName, @cCusPassword, @cCusRealName, @iCusPermissions, @cCusUserKey,@dCusCreateTime);
                        //SELECT SCOPE_IDENTITY();";
                        int iCusID;
                        using (SqlCommand cmd = new SqlCommand(insertMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cCusName", users.cCusName ?? "");
                            //cmd.Parameters.AddWithValue("@cCusPassword", users.cCusPassword ?? "");
                            //cmd.Parameters.AddWithValue("@cCusRealName", users.cCusRealName ?? "");
                            cmd.Parameters.AddWithValue("@iCusPermissions", users.iCusPermissions);
                            cmd.Parameters.AddWithValue("@cCusUserKey", users.cCusUserKey ?? "");
                            //cmd.Parameters.AddWithValue("@dCusCreateTime", user.dCusCreateTime == default ? (object)DBNull.Value : user.dCusCreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                            cmd.Parameters.AddWithValue("@dCusCreateTime", users.dCusCreateTime == default ? (object)DBNull.Value : users.dCusCreateTime);
                            //cmd.Parameters.AddWithValue("@Salt", users.Salt ?? "");
                            iCusID = Convert.ToInt32(cmd.ExecuteScalar());
                        }
                        // 插入密钥表 (Licenses)
                        string insertDetailSql = @"INSERT INTO Licenses (cCusName, cCusLicenses, dCusExpiryDate) VALUES (@cCusName, @cCusLicenses, @dCusExpiryDate);";
                        using (SqlCommand cmd = new SqlCommand(insertDetailSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cCusName", users.cCusName ?? "");
                            cmd.Parameters.AddWithValue("@cCusLicenses", users.cCusLicenses ?? "");
                            cmd.Parameters.AddWithValue("@dCusExpiryDate", users.dCusExpiryDate == default ? (object)DBNull.Value : users.dCusExpiryDate);
                            int detailRows = cmd.ExecuteNonQuery();
                            if (detailRows == 0) throw new Exception("用户密钥表插入失败");
                        }

                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        //修改密码
        public bool UpdatePassword(UserPasswordDTO userUpdatePassword)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        ////查询盐值
                        //string saltQuery = "SELECT Salt FROM [User] WHERE cCusName = @cCusName";
                        //string storedSalt = null;
                        //using (SqlCommand cmd = new SqlCommand(saltQuery, conn, transaction))
                        //{
                        //    cmd.Parameters.AddWithValue("@cCusName", userUpdatePassword.cCusName);
                        //    SqlDataReader reader = cmd.ExecuteReader();
                        //    if (reader.Read())
                        //    {
                        //        storedSalt = reader["Salt"].ToString();
                        //    }
                        //    reader.Close();
                        //}
                        // 生成随机盐值 
                        var salt = PasswordService.GenerateSalt();
                        var finalHash = PasswordService.HashPassword(userUpdatePassword.cCusPassword, salt);
                        // 更新密码
                        string updatePasswordSql = @"UPDATE [User]  SET cCusPassword = @cCusPassword, Salt = @Salt  WHERE cCusName = @cCusName";
                        using (SqlCommand cmd = new SqlCommand(updatePasswordSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cCusName", userUpdatePassword.cCusName);
                            cmd.Parameters.AddWithValue("@cCusPassword", finalHash);
                            cmd.Parameters.AddWithValue("@Salt", salt);
                            int rowsAffected = cmd.ExecuteNonQuery();
                            if (rowsAffected == 0) throw new Exception("用户密码更新失败");
                        }
                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }


        //修改信息
        public bool UpdateUser(UserUpdate user)
        {
            string connStr = _configuration.GetConnectionString("Default");

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 更新主表 (Users)
                        string updateMainSql = @"
                        UPDATE CusUserList
                        SET cCusName = @cCusName, 
                            cCusUserKey = @cCusUserKey
                        WHERE iCusID = @iCusID";

                        using (SqlCommand cmd = new SqlCommand(updateMainSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@iCusID", user.iCusID);
                            cmd.Parameters.AddWithValue("@cCusName", user.cCusName ?? "");
                            //cmd.Parameters.AddWithValue("@cCusPassword", user.cCusPassword ?? "");
                            //cmd.Parameters.AddWithValue("@cCusRealName", user.cCusRealName ?? "");
                            cmd.Parameters.AddWithValue("@cCusUserKey", user.cCusUserKey ?? "");

                            int mainRows = cmd.ExecuteNonQuery();
                            if (mainRows == 0) throw new Exception("用户主表更新失败");
                        }

                        // 更新密钥表 (Licenses)
                        string updateDetailSql = @"
                        UPDATE Licenses
                        SET cCusLicenses = @cCusLicenses, 
                            dCusExpiryDate = @dCusExpiryDate OUTPUT INSERTED.iCusLicensesID
                        WHERE cCusName = @cCusName;";
                        int iCusLicensesID;


                        using (SqlCommand cmd = new SqlCommand(updateDetailSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cCusName", user.cCusName);
                            cmd.Parameters.AddWithValue("@cCusLicenses", user.cCusLicenses ?? "");
                            //cmd.Parameters.AddWithValue("@dCusExpiryDate", user.dCusExpiryDate == default ? (object)DBNull.Value : user.dCusExpiryDate);
                            cmd.Parameters.AddWithValue("@dCusExpiryDate", user.dCusExpiryDate == default ? (object)DBNull.Value : user.dCusExpiryDate.ToDateTime(TimeOnly.MinValue));

                            object result = cmd.ExecuteScalar(); // 直接获取 OUTPUT 的值
                            if (result == null || result == DBNull.Value)
                            {
                                throw new Exception("未能获取 Licenses 表的 iCusLicensesID");
                            }
                            iCusLicensesID = (int)result;
                            int detailRows = cmd.ExecuteNonQuery();
                        }

                        //修改秘钥表用户名
                        //string updateLicensesSql = @"update Licenses set cCusName = @cCusName where iCusLicensesID = @iCusLicensesID";
                        //if (user.cCusName != user.oldCusName)
                        //    using (SqlCommand cmd = new SqlCommand(updateLicensesSql, conn, transaction))
                        //    {
                        //        cmd.Parameters.AddWithValue("@cCusName", user.cCusName ?? "");
                        //        cmd.Parameters.AddWithValue("@iCusLicensesID", iCusLicensesID);
                        //        int detailRows = cmd.ExecuteNonQuery();
                        //    }


                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }

        }


        public bool DeleteUser(string cCusName)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 先删除密钥表
                        string deleteLicensesSql = "DELETE FROM Licenses WHERE cCusName = @cCusName";
                        using (SqlCommand cmd = new SqlCommand(deleteLicensesSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cCusName", cCusName);
                            cmd.ExecuteNonQuery();
                        }

                        // 再删除用户表
                        string deleteUserSql = "DELETE FROM CusUserList WHERE cCusName = @cCusName";
                        using (SqlCommand cmd = new SqlCommand(deleteUserSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@cCusName", cCusName);
                            int affectedRows = cmd.ExecuteNonQuery();
                            if (affectedRows == 0)
                                throw new Exception("未找到要删除的用户");
                        }

                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        public bool Uploadimage(string fileName, string filePath)
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                // 开启事务 
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 更新用户头像路径
                        string updateImageSql = "UPDATE Images SET image = @FilePath WHERE id = 1";
                        using (SqlCommand cmd = new SqlCommand(updateImageSql, conn, transaction))
                        {
                            cmd.Parameters.AddWithValue("@FilePath", filePath);
                            cmd.ExecuteNonQuery();
                        }
                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        public string GetImageUrl()
        {
            string connStr = _configuration.GetConnectionString("Default");
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                string query = "SELECT image FROM Images WHERE id = 1";
                using (SqlCommand command = new SqlCommand(query, conn))
                {
                    object result = command.ExecuteScalar();
                    return result != null ? result.ToString() : null;
                }
            }
        }
    }
}
